﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Agresso.Interface.Layer.SQL
{
    public class CompasSQL
    {
        public static string GetAllPendingAGRESO_SYNC()
        {
            return @"select * from AGRESO_SYNC where AS_IS_EXPORTED is null and AS_COMPANY_CODE in ('203','401') order by AS_VESSEL_ACC_CODE";
//                @"select * from AGRESO_SYNC where 
//AS_IS_EXPORTED is null 
//and AS_MONTH = 3
//and AS_YEAR = 2014
//and AS_VESSEL_NAME in
//(
//'PAC SHAULA',
//'ATLANTIC PROGRESS'
//) order by AS_VESSEL_ACC_CODE";
        }
        public static string GetAllPendingAGRESO_SYNC(int month, int year, string[] vesselname, bool exported)
        {
            string vessel = "";
            int index= 0;
            foreach (string vesseln in vesselname)
            {
                if (index == 0)
                    vessel += "'" + vesseln + "'";
                else
                    vessel += ",'" + vesseln + "'";
                index++;
            }
            return @"select * from AGRESO_SYNC where 
            AS_IS_EXPORTED " + (exported ? "='1'" : "is null") + @" 
            and AS_MONTH = "+month.ToString()+@"
            and AS_YEAR = "+year.ToString()+@"
            and AS_VESSEL_NAME in
            (" + vessel + @"
            ) order by AS_VESSEL_ACC_CODE";
        }
        public static string GetAllPendingStagingAGRESO_SYNC()
        {
            return @"select * from AGRESO_SYNC where AS_IS_EXPORTED is null 
and AS_YEAR = 2013 and AS_MONTH = 11
and AS_COMPANY_CODE = '603'";
        }
        public static string GetAllAGRESO_SYNCByID()
        {
            return @"select * from AGRESO_SYNC where AS_ID = @AS_ID";
        }

        public static string UpdateAGRESO_SYNCStatus()
        {
            return @"update AGRESO_SYNC set AS_IS_EXPORTED=@AS_IS_EXPORTED
                where AS_ID=@AS_ID";//, AS_PICKUP_DATE_TIME=@AS_PICKUP_DATE_TIME
        }
        public static string GetAllPendingFromIntermediate()
        {
            return @"select * from I_AGRESO_SYNC where  batch_id in 
            (
'CMS603201403121500',
'CMS603201403121501',
'CMS603201403121502',
'CMS603201403121503',
'CMS603201403121504',
'CMS603201403121505',
'CMS603201403121506',
'CMS603201403121507',
'CMS603201403121508',
'CMS603201403121509',
'CMS603201403121510',
'CMS603201403121511',
'CMS603201403121512',
'CMS603201403121513',
'CMS603201403121514',
'CMS603201403121515',
'CMS603201403121516',
'CMS603201403121517',
'CMS603201403121518',
'CMS603201403121519',
'CMS603201403121520',
'CMS603201403121521',
'CMS603201403121522',
'CMS603201403121523',
'CMS603201403121524',
'CMS603201403121525',
'CMS603201403121526',
'CMS603201403121527',
'CMS603201403121528',
'CMS603201403121529',
'CMS603201403121530',
'CMS603201403121531',
'CMS603201403121532',
'CMS603201403121533',
'CMS603201403121534',
'CMS603201403121535',
'CMS603201403121536',
'CMS603201403121537',
'CMS603201403121538',
'CMS603201403121539'
)
            ";//= 'CMS603201403071128' ";//AS_IS_EXPORTED = 0";
        }
    }
}
